package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.Expediente;
import Interface.ExpedienteDAO;
import Utiles.MySqlDBConexion;

public class MySqlExpedienteDAO implements ExpedienteDAO{
	
	Connection cn= null;
	CallableStatement cstmt=null;
	List<List<Object>> lis_global = new ArrayList<List<Object>>();
	List<Object> lis_tmp; 
	ResultSet rs=null;
	PreparedStatement ps=null;
	String sql ="";
	
	
	@Override
	public Expediente BuscarExpedienteID(String id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void ModificarExpediente(Expediente obj) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void EliminarExpediente(Expediente obj) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Expediente> ListarExpediente() {
		List <Expediente> lisArt= new ArrayList<Expediente>();
		cn = MySqlDBConexion.getConexion();
		
		try {
			String consulta = "select * from tb_exp_cab";
			ps= cn.prepareStatement(consulta);
			rs = ps.executeQuery();
			
			while (rs.next()){
				Expediente obj = new Expediente(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9));
			    lisArt.add(obj);
			}
			cn.close();
			
		}catch(SQLException s){
			s.printStackTrace();
			
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return lisArt;
	}


	
	

	@Override
	public int RegistrarExpediente(Expediente obj) {
		int resultado =0;
		   Connection cn= MySqlDBConexion.getConexion();
		try {
			String sql = "insert into Expediente(NUMERO,ASUNTO,FECHA,ESTADO) values (?,?,?,?)";
			PreparedStatement ps;
				ps = cn.prepareStatement(sql);
				ps.setString(1, obj.getNumero());
				ps.setString(2, obj.getAsunto());
				ps.setString(3,  obj.getFecha());
				ps.setString(4, obj.getEstado());

				resultado=ps.executeUpdate();
				
			    cn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
	   return resultado;	    
	}


	@Override
	public List<List<Object>> listaCotizacion(String lista) {
	
		cn= MySqlDBConexion.getConexion();
		
		try {		
			sql="{call SPT_LISTAR_COT_CADENA (?)}";
			cstmt = cn.prepareCall (sql);
	         
			cstmt.setString(1,lista);
			
			 rs=cstmt.executeQuery();
			
			while (rs.next()) {
				lis_tmp= new ArrayList<Object>();
				lis_tmp.add(rs.getString(1));
				lis_tmp.add(rs.getString(2));
				lis_tmp.add(rs.getString(3));
				lis_tmp.add(rs.getString(4));
				lis_tmp.add(rs.getString(5));
				lis_tmp.add(rs.getString(6));
				lis_tmp.add(rs.getString(7));
				lis_tmp.add(rs.getString(8));
				lis_tmp.add(rs.getString(9));
				lis_tmp.add(rs.getString(10));
				lis_global.add(lis_tmp);
			}
			cn.close();
		} catch (Exception e) {
		e.printStackTrace();
		}
		return lis_global;

	}
	
	
	
	

}
